﻿using System.Collections.Concurrent;
using System.Diagnostics;
using System.Text.Json;
using System.Globalization;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ExcelExportCS;

/// <summary>
/// 表格类型
/// </summary>
public enum SheetType
{
    None, // 无效
    Primary, // 主建表
    Param, // 参数表
    Group, // 分组表
}

/// <summary>
/// 字段类型
/// </summary>
public enum FieldType
{
    Int, // 数值型
    Int64, // 长数值型
    Float, // 浮点型
    Double, // 双精度型
    String, // 字符串型
    Array, // json数组
    Language, // 语言包
}

/// <summary>
/// 字段定义
/// </summary>
public class SheetField
{
    public string Key { get; set; } = string.Empty;
    public string Desc { get; set; } = string.Empty;
    public int CellIndex { get; set; }
    public FieldType Type { get; set; }
    public bool ExportClient { get; set; }
    public bool ExportServer { get; set; }
}

/// <summary>
/// 表格元数据定义
/// </summary>
public class SheetMeta
{
    public string MainKey { get; set; } = string.Empty;
    public string FileName { get; init; } = string.Empty;
    public string SheetName { get; init; } = string.Empty;
    public bool ExportClient { get; init; }
    public bool ExportServer { get; init; }
    public SheetType Type { get; init; }
    public Dictionary<int, SheetField> Fields { get; } = new();
    public List<object> SheetRowData { get; } = new(4096);
}

public partial class ExcelReader(string moduleName, string excelDirectory, string clientDirectory, string serverDirectory)
{
    /// <summary>
    /// 已完成读取的文件名列表
    /// </summary>
    private readonly ConcurrentQueue<string> _doneClientFiles = [];

    private readonly ConcurrentQueue<string> _doneServerFiles = [];

    /// <summary>
    /// 模块名称
    /// </summary>
    private string ModuleName { get; } = moduleName;

    /// <summary>
    /// 表格所在路径
    /// </summary>
    private string ExcelDirectory { get; } = excelDirectory;

    /// <summary>
    /// 客户端导出路径, 服务端导出路径
    /// </summary>
    private string ClientDirectory { get; set; } = clientDirectory;

    /// <summary>
    /// 配置表class客户端导出路径
    /// </summary>
    private string ExportJsonClientDirectory { get; set; } = string.Empty;

    private string ExportTemplateClientDirectory { get; set; } = string.Empty;
    private string ServerDirectory { get; set; } = serverDirectory;

    /// <summary>
    /// 配置表class服务端导出路径
    /// </summary>
    private string ExportJsonServerDirectory { get; set; } = string.Empty;

    private string ExportTemplateServerDirectory { get; set; } = string.Empty;

    /// <summary>
    /// 文件总数
    /// </summary>
    private long FileCount { get; set; }

    /// <summary>
    /// 已成功导出文件数
    /// </summary>
    private long _fileDoneCount;

    private long FileDoneCount => _fileDoneCount;

    /// <summary>
    /// 表格类型字典
    /// </summary>
    private static readonly Dictionary<string, SheetType> SheetTypeDictionary = new()
    {
        { "1", SheetType.Primary },
        { "主建表", SheetType.Primary },
        { "主键表", SheetType.Primary },
        { "2", SheetType.Param },
        { "参数表", SheetType.Param },
        { "分组表", SheetType.Group }
    };

    /// <summary>
    /// 字段类型字典
    /// </summary>
    private static readonly Dictionary<string, FieldType> FieldTypeDictionary = new()
    {
        { "int", FieldType.Int },
        { "number", FieldType.Int },
        { "string", FieldType.String },
        { "[]", FieldType.Array },
        { "array", FieldType.Array },
        { "language", FieldType.Language }
    };

    /// <summary>
    /// 删除目录并重新创建
    /// </summary>
    private static void InitAndClearDirectory(string path)
    {
        if (Directory.Exists(path))
        {
            Directory.Delete(path, true);
        }

        Directory.CreateDirectory(path);
    }

    /// <summary>
    /// 匹配表格类型
    /// </summary>
    private static SheetType MatchSheetType(string type)
    {
        return SheetTypeDictionary.GetValueOrDefault(type, SheetType.None);
    }

    /// <summary>
    /// 匹配字段类型
    /// </summary>
    private static FieldType MatchFieldType(string type)
    {
        return FieldTypeDictionary.GetValueOrDefault(type, FieldType.String);
    }

    /// <summary>
    /// main表必须要带的字段
    /// </summary>
    private static readonly List<string> MainSheetKeyDefine = ["file", "name", "client", "server", "type"];

    /// <summary>
    /// 读取所有配置表并进行导出
    /// </summary>
    public void Walk()
    {
        if (!Directory.Exists(ExcelDirectory)) return;

        var watcher = new Stopwatch();
        watcher.Start();

        var files = Directory.EnumerateFiles(ExcelDirectory, $"*.xlsx", SearchOption.AllDirectories)
            .Where(file => !file.Contains("~$"))
            .ToList();
        FileCount = files.Count;

        ExportTemplateClientDirectory = string.Join(Path.DirectorySeparatorChar, [ClientDirectory, "ExcelDefine"]);
        ExportTemplateServerDirectory = string.Join(Path.DirectorySeparatorChar, [ServerDirectory, "ExcelDefine"]);
        ExportJsonClientDirectory = string.Join(Path.DirectorySeparatorChar, [ExportTemplateClientDirectory, "Json"]);
        ExportJsonServerDirectory = string.Join(Path.DirectorySeparatorChar, [ExportTemplateServerDirectory, "Json"]);
        InitAndClearDirectory(ExportTemplateClientDirectory);
        InitAndClearDirectory(ExportTemplateServerDirectory);
        InitAndClearDirectory(ExportJsonClientDirectory);
        InitAndClearDirectory(ExportJsonServerDirectory);

        // Parallel.ForEach(files, new ParallelOptions() { MaxDegreeOfParallelism = 1 }, ProcessReadExcelFile);
        Parallel.ForEach(files, ProcessReadExcelFile);

        var exportInterfaceText = string.Format(Template.ExcelClassInterfaceFmt, ModuleName);
        File.WriteAllText(Path.Combine(ExportTemplateClientDirectory, Template.ExcelClassFileName), exportInterfaceText);
        File.WriteAllText(Path.Combine(ExportTemplateServerDirectory, Template.ExcelClassFileName), exportInterfaceText);

        ExportWriter.WriteConfigMgrFile(ModuleName, _doneClientFiles.ToList(), ExportTemplateClientDirectory);
        ExportWriter.WriteConfigMgrFile(ModuleName, _doneServerFiles.ToList(), ExportTemplateServerDirectory);

        watcher.Stop();
        Console.WriteLine($"已处理完成 {FileDoneCount}/{FileCount} 个文件 耗时:{watcher.ElapsedMilliseconds}毫秒");
    }

    private void ProcessReadExcelFile(string file, ParallelLoopState state, long fileIndex)
    {
        var shortFileName = Path.GetFileName(file);

        using var wb = SpreadsheetDocument.Open(file, false);
        var mainSheet = wb.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
        if (mainSheet == null)
        {
            Console.WriteLine($"读取{fileIndex + 1}/{FileCount}:{shortFileName}, sheet表数量:0");
            return;
        }

        if (!ReadMainSheet(wb.WorkbookPart, (WorksheetPart)wb.WorkbookPart.GetPartById(mainSheet.Id), out var metaDict, out var err))
        {
            Console.WriteLine($"读取{fileIndex + 1}/{FileCount}:{shortFileName}, main表错误:{err}");
            return;
        }

        var clientMeta = 0;
        var serverMeta = 0;
        foreach (var sheet in wb.WorkbookPart.Workbook.Descendants<Sheet>())
        {
            if (!metaDict.TryGetValue(sheet.Name, out var meta))
            {
                continue;
            }

            switch (meta.Type)
            {
                case SheetType.Primary:
                case SheetType.Group:
                    if (!ReadPrimarySheet(wb.WorkbookPart, (WorksheetPart)wb.WorkbookPart.GetPartById(sheet.Id), meta, out err))
                    {
                        Console.WriteLine($"读取{fileIndex + 1}/{FileCount}:{shortFileName}, 错误:{err}");
                    }

                    break;
                case SheetType.Param:
                    if (!ReadParamSheet(wb.WorkbookPart, (WorksheetPart)wb.WorkbookPart.GetPartById(sheet.Id), meta, out err))
                    {
                        Console.WriteLine($"读取{fileIndex + 1}/{FileCount}:{shortFileName}, 错误:{err}");
                    }

                    break;
                case SheetType.None:
                default:
                    Console.WriteLine($"读取{fileIndex + 1}/{FileCount}:{shortFileName}, 错误:{sheet.Name}不受支持的表类型");
                    break;
            }

            if (meta.ExportClient)
            {
                clientMeta++;
            }

            if (meta.ExportServer)
            {
                serverMeta++;
            }
        }

        Interlocked.Increment(ref _fileDoneCount);

        ExportWriter.WriteCSharpFile(ModuleName, shortFileName, metaDict, ExportTemplateClientDirectory, ExportTemplateServerDirectory, true);
        ExportWriter.WriteCSharpFile(ModuleName, shortFileName, metaDict, ExportTemplateClientDirectory, ExportTemplateServerDirectory, false);
        ExportWriter.WriteJsonToFile(shortFileName, metaDict, ExportJsonClientDirectory, ExportJsonServerDirectory);

        if (ExportWriter.MatchExportFileName(shortFileName, out var exportFileName) && exportFileName != null)
        {
            if (clientMeta > 0)
            {
                _doneClientFiles.Enqueue(exportFileName);
            }

            if (serverMeta > 0)
            {
                _doneServerFiles.Enqueue(exportFileName);
            }
        }

        Console.WriteLine($"读取{fileIndex + 1}/{FileCount}:{shortFileName}, 数量:{metaDict.Count}, [{string.Join(", ", metaDict.Values.Select(meta => meta.SheetName))}]");
    }

    /// <summary>
    /// 获取表格最大行数
    /// </summary>
    private static int GetSheetMaxRow(WorksheetPart wbSheet)
    {
        return wbSheet.Worksheet.Descendants<Row>().Count();
    }

    /// <summary>
    /// 获取表格最大列数
    /// </summary>
    private static int GetSheetMaxCol(WorksheetPart wbSheet)
    {
        return wbSheet.Worksheet.Descendants<Row>()
            .Select(r => r.Descendants<Cell>().Count())
            .DefaultIfEmpty(0)
            .Max();
    }

    /// <summary>
    /// 获取单元格所在列 从0开始
    /// </summary>
    private static int GetCellIndex(Cell cell)
    {
        string cellRef = cell.CellReference;
        var columnLetter = new string(cellRef
            .TakeWhile(c => c is >= 'A' and <= 'Z' or >= 'a' and <= 'z')
            .ToArray());
        return columnLetter.ToUpper().Aggregate(0, (current, c) => current * 26 + (c - 'A' + 1)) - 1;
    }

    /// <summary>
    /// 获取单元格值
    /// </summary>
    private static string GetCellValue(Cell? cell, WorkbookPart workbookPart)
    {
        if (cell == null) return "";
        if (cell.DataType is not { Value: CellValues.SharedString }) return cell.CellValue?.Text ?? "";
        var sharedStringTable = workbookPart.SharedStringTablePart?.SharedStringTable;
        return sharedStringTable?.ElementAt(int.Parse(cell.CellValue.Text))?.InnerText ?? "";
    }

    /// <summary>
    /// 转换单元格值
    /// </summary>
    private static object TransformCellValue(string cellValue, FieldType fieldType, out FieldType? valueType)
    {
        valueType = null;
        object value = cellValue;
        switch (fieldType)
        {
            case FieldType.Int:
            case FieldType.Int64:
            case FieldType.Float:
            case FieldType.Double:
                if (int.TryParse(cellValue, NumberStyles.Any, CultureInfo.InvariantCulture, out var i32Value))
                {
                    value = i32Value;
                    valueType = FieldType.Int;
                    break;
                }

                if (long.TryParse(cellValue, NumberStyles.Any, CultureInfo.InvariantCulture, out var i64Value))
                {
                    value = i64Value;
                    valueType = FieldType.Int64;
                    break;
                }

                if (float.TryParse(cellValue, NumberStyles.Any, CultureInfo.InvariantCulture, out var fValue))
                {
                    value = fValue;
                    valueType = FieldType.Float;
                    break;
                }

                if (double.TryParse(cellValue, NumberStyles.Any, CultureInfo.InvariantCulture, out var dValue))
                {
                    value = dValue;
                    valueType = FieldType.Double;
                    break;
                }

                value = decimal.TryParse(cellValue, out var nValue) ? nValue : 0;
                break;
            case FieldType.Array:
                try
                {
                    value = JsonSerializer.Deserialize<List<object>>(cellValue) ?? [];
                }
                catch (Exception)
                {
                    if (cellValue == "")
                    {
                        value = new List<object>();
                    }
                }

                break;
            case FieldType.Language:
            case FieldType.String:
            default:
                value = cellValue;
                break;
        }

        return value;
    }

    /// <summary>
    /// 读取main主表
    /// </summary>
    private static bool ReadMainSheet(WorkbookPart wbPart, WorksheetPart wbSheet,
        out Dictionary<string, SheetMeta> metaDict, out string? err)
    {
        err = null;
        metaDict = new Dictionary<string, SheetMeta>();
        var row = GetSheetMaxRow(wbSheet);
        if (row < 2)
        {
            err = "main表行数小于2行";
            return false;
        }

        var col = GetSheetMaxCol(wbSheet);
        if (col < MainSheetKeyDefine.Count + 1)
        {
            err = $"main表列数小于{MainSheetKeyDefine.Count + 1}列";
            return false;
        }

        var firstRow = wbSheet.Worksheet.Descendants<Row>().FirstOrDefault();
        if (firstRow == null)
        {
            err = "main表第一行不存在";
            return false;
        }

        // 首行: file(导出key名),name(sheet名),client(是否导出客户端YES/NO),server(是否导出服务端YES/NO),type(表格类型字符串对应SheetType)
        var columnDict = new Dictionary<string, int>();
        foreach (var cell in firstRow.Descendants<Cell>())
        {
            var value = GetCellValue(cell, wbPart);
            if (!MainSheetKeyDefine.Contains(value))
            {
                continue;
            }

            columnDict[value] = GetCellIndex(cell);
        }

        // 读取每一行
        foreach (var readRow in wbSheet.Worksheet.Descendants<Row>())
        {
            if (GetCellValue(readRow.Descendants<Cell>().ElementAtOrDefault(0), wbPart) == "END")
            {
                continue;
            }

            var type = MatchSheetType(GetCellValue(readRow.Descendants<Cell>().ElementAtOrDefault(columnDict["type"]), wbPart));
            if (type == SheetType.None)
            {
                continue;
            }

            var exportClient = GetCellValue(readRow.Descendants<Cell>().ElementAtOrDefault(columnDict["client"]), wbPart) == "YES";
            var exportServer = GetCellValue(readRow.Descendants<Cell>().ElementAtOrDefault(columnDict["server"]), wbPart) == "YES";
            if (!exportClient && !exportServer)
            {
                continue;
            }

            var meta = new SheetMeta
            {
                FileName = GetCellValue(readRow.Descendants<Cell>().ElementAt(columnDict["file"]), wbPart),
                SheetName = GetCellValue(readRow.Descendants<Cell>().ElementAt(columnDict["name"]), wbPart),
                ExportClient = exportClient,
                ExportServer = exportServer,
                Type = type
            };
            metaDict[meta.SheetName] = meta;
        }

        return true;
    }

    /// <summary>
    /// 读取主建表
    /// </summary>
    private static bool ReadPrimarySheet(WorkbookPart wbPart, WorksheetPart wbSheet, SheetMeta meta, out string? err)
    {
        err = null;
        var row = GetSheetMaxRow(wbSheet);
        if (row < 2)
        {
            err = $"{meta.SheetName}表行数小于2行";
            return false;
        }

        var col = GetSheetMaxCol(wbSheet);
        if (col < 2)
        {
            err = $"{meta.SheetName}表列数小于2列";
            return false;
        }

        // 读取每一行
        foreach (var readRow in wbSheet.Worksheet.Descendants<Row>())
        {
            var flag = GetCellValue(readRow.Descendants<Cell>().ElementAtOrDefault(0), wbPart);
            if (flag == "END")
            {
                break;
            }

            if (flag == "0")
            {
                continue;
            }

            foreach (var readCol in readRow.Descendants<Cell>())
            {
                var c = GetCellIndex(readCol);
                var cellValue = GetCellValue(readCol, wbPart);
                if (cellValue == "END")
                {
                    break;
                }

                if (readRow.RowIndex == 1)
                {
                    meta.Fields[c] = new SheetField()
                    {
                        Key = cellValue,
                        Desc = "",
                        CellIndex = c,
                        Type = FieldType.String,
                        ExportClient = false,
                        ExportServer = false
                    };
                }

                if (!meta.Fields.TryGetValue(c, out var field))
                {
                    continue;
                }

                switch (flag)
                {
                    case "数据类型":
                        field.Type = MatchFieldType(cellValue);
                        break;
                    case "客户端":
                        field.ExportClient = cellValue == "YES";
                        break;
                    case "服务端":
                        field.ExportServer = cellValue == "YES";
                        break;
                    case "名称":
                        field.Desc = cellValue;
                        break;
                    case "1":
                        switch (c)
                        {
                            case 0:
                                continue;
                            case 1:
                                meta.SheetRowData.Add(new List<object>(Enumerable.Repeat<object>(0, meta.Fields.Count)));
                                break;
                        }

                        var rValue = TransformCellValue(cellValue, field.Type, out var valueType);
                        if (valueType != null)
                        {
                            field.Type = valueType.Value;
                        }

                        (meta.SheetRowData[^1] as List<object>)?.Insert(field.CellIndex, rValue);
                        break;
                }
            }
        }

        for (var c = 1; c < col; c++)
        {
            if (!meta.Fields.TryGetValue(c, out var field))
            {
                continue;
            }

            if (field is { ExportClient: false, ExportServer: false })
            {
                continue;
            }

            if (field.Type != FieldType.Int)
            {
                continue;
            }

            meta.MainKey = field.Key;
            // 强制id模式
            if (field.Key != "id" && field.Key != "Id")
            {
                meta.MainKey = field.Key;
            }

            break;
        }

        return true;
    }

    /// <summary>
    /// 读取参数表
    /// </summary>
    private static bool ReadParamSheet(WorkbookPart wbPart, WorksheetPart wbSheet, SheetMeta meta, out string? err)
    {
        err = null;
        var row = GetSheetMaxRow(wbSheet);
        if (row < 2)
        {
            err = $"{meta.SheetName}表行数小于2行";
            return false;
        }

        var col = GetSheetMaxCol(wbSheet);
        if (col < 2)
        {
            err = $"{meta.SheetName}表列数小于2列";
            return false;
        }

        var paramDict = new Dictionary<int, string>();
        // 读取每一行
        for (var r = 0; r < row; r++)
        {
            var readRow = wbSheet.Worksheet.Descendants<Row>().ElementAt(r);
            if (readRow == null)
            {
                continue;
            }

            var flag = GetCellValue(readRow.Descendants<Cell>().ElementAtOrDefault(0), wbPart);
            if (flag == "END")
            {
                break;
            }

            if (flag == "0")
            {
                continue;
            }

            var rowData = new SheetField();
            for (var c = 1; c < col; c++)
            {
                var readCol = readRow.Descendants<Cell>().ElementAtOrDefault(c);
                if (readCol == null)
                {
                    continue;
                }

                var cellValue = GetCellValue(readCol, wbPart);
                if (cellValue == "END")
                {
                    break;
                }

                if (readRow.RowIndex == 1)
                {
                    paramDict[c] = cellValue;
                    continue;
                }

                if (!paramDict.TryGetValue(c, out var words))
                {
                    continue;
                }

                if (flag != "1")
                {
                    break;
                }

                switch (words)
                {
                    case "key":
                        rowData.Key = cellValue;
                        break;
                    case "数据类型":
                        rowData.Type = MatchFieldType(cellValue);
                        break;
                    case "客户端":
                        rowData.ExportClient = cellValue == "YES";
                        break;
                    case "服务端":
                        rowData.ExportServer = cellValue == "YES";
                        break;
                    case "注释":
                        rowData.Desc = cellValue;
                        break;
                    case "value":
                        var rValue = TransformCellValue(cellValue, rowData.Type, out var valueType);
                        if (valueType != null)
                        {
                            rowData.Type = valueType.Value;
                        }

                        meta.SheetRowData?.Add(rValue);
                        rowData.CellIndex = meta.Fields.Count;
                        meta.Fields[meta.Fields.Count] = rowData;
                        break;
                }
            }
        }

        return true;
    }
}